﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

namespace ExcelDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            String excelPath = "../../ExcelData.xlsx";
            //设置规则，这一段建议写在配置文件里，用或符号分隔枚举匹配
            Hashtable strategies = new Hashtable();
            strategies.Add("EmployeeNumber", "工号");
            strategies.Add("IdentityNumber", "身份证|证件号");
            strategies.Add("Name", "姓名|名字");
            strategies.Add("Workplace", "工作地");
            //读取excel
            List<User> userList = ReadExcel<User>(excelPath, strategies);
            //输出
            Console.WriteLine("工号" + "\t" + "身份证|证件号" + "\t" + "姓名|名字" + "\t" + "工作地");
            foreach (var user in userList)
            {
                Console.WriteLine(user.EmployeeNumber + "\t" + user.Name + "\t" + user.IdentityNumber + "\t" + user.Workplace);
            }
            Console.ReadLine();
        }

        /// <summary>
        /// 读取excel并返回匹配的对象列表
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="filePath">excel路径（相对路径或绝对路径）</param>
        /// <param name="strategies">匹配规则</param>
        /// <returns>对象列表</returns>
        static List<T> ReadExcel<T>(String filePath, Hashtable strategies) where T : new()
        {
            try
            {
                //声明变量
                List<T> resultList = new List<T>();
                IWorkbook workbook = null;
                ISheet sheet = null;
                FileStream fs = null;
                //获取excel文件
                fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (filePath.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                sheet = workbook.GetSheetAt(0);//第一张表
                if (sheet != null)
                {
                    String[,] sheetContant = ReadExcelSheetContant(sheet);//把表内容加载到内存

                    //将表内容对应到对象上
                    if (sheetContant != null)
                    {
                        //TODO：这里需要重构优化
                        for (int i = 1; i < sheetContant.GetLength(0); i++)
                        {//遍历行
                            T result = new T();
                            foreach (var propertie in result.GetType().GetProperties())
                            {//遍历对象属性
                                String[] cellNames = strategies[propertie.Name].ToString().Split('|');//用或分隔枚举匹配
                                for (int j = 0; j < cellNames.Length; j++)
                                {//遍历列
                                    for (int k = 0; k < sheetContant.GetLength(1); k++)
                                    {//比较属性
                                        if (cellNames[j].Equals(sheetContant[0, k]))//如果要模糊匹配这里改成模糊匹配就可以了，但是建议用枚举匹配
                                        {
                                            result.GetType().GetProperty(propertie.Name).SetValue(result, sheetContant[i, k]);
                                        }
                                    }
                                }
                            }
                            resultList.Add(result);
                        }
                    }


                }
                workbook.Close();
                fs.Close();
                return resultList;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                //TODO：这里写log
                return null;
            }
        }


        /// <summary>
        /// 获取表内容
        /// </summary>
        /// <param name="sheet">表</param>
        /// <returns>表内容</returns>
        static String[,] ReadExcelSheetContant(ISheet sheet)
        {
            try
            {
                int rowNumber = CountExcelSheetRowNumber(sheet);
                int cellNumber = CountExcelSheetCellNumber(sheet);
                String[,] sheetContant = new String[rowNumber, cellNumber];
                for (int i = 0; i < rowNumber; i++)
                {
                    for (int j = 0; j < cellNumber; j++)
                    {
                        if (sheet.GetRow(i) != null && sheet.GetRow(i).GetCell(j) != null)
                            sheetContant[i, j] = sheet.GetRow(i).GetCell(j).ToString();
                    }
                }
                return sheetContant;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                //TODO：这里写log
                return null;
            }
        }
        /// <summary>
        /// 统计表行数
        /// </summary>
        /// <param name="sheet">表</param>
        /// <returns>行数</returns>
        static int CountExcelSheetRowNumber(ISheet sheet)
        {
            int i = 0;
            if (sheet != null)
            {
                while (true)
                {
                    if (sheet.GetRow(i) == null)
                    {
                        //TODO:未考虑第一行为空或两行之间空一行的情况
                        break;
                    }
                    else
                    {
                        i++;
                    }
                }
            }
            return i;
        }

        /// <summary>
        /// 统计表列数
        /// </summary>
        /// <param name="sheet">表</param>
        /// <returns>列数</returns>
        static int CountExcelSheetCellNumber(ISheet sheet)
        {
            int i = 0;
            if (sheet != null && sheet.GetRow(0) != null)
            {
                while (true)
                {
                    if (sheet.GetRow(0).GetCell(i) == null)
                    {
                        //TODO:未考虑第一列为空或两列之间空一列的情况
                        break;
                    }
                    else
                    {
                        i++;
                    }
                }
            }
            return i;
        }
    }

}
